Slowly Change Dimensions (SCD)

Slowly Change Dimension Type 1 (SCD Type 1)

在上一篇 B2B 會議室商品的個案中，有一張表格叫 dim_company ，裡面的每一列都是個案的（潛在）客戶。而裡面呢，有一間客戶叫 McBurger，我想看他的詳細資訊，執行下面的SQL ：
select * from dim_company where company_name = 'McBurger'，就可以得到跟這間公司有關的資訊：

如果台北市的業績負責人想要追蹤月營收的狀況 ，可以執行下面的SQL並進行視覺化：

select
date_trunc('month', dim_date.actual_date) as year_month, 
dim_company.city,
coalesce(sum(fct_order_item.sales_amount), 0) as amount 
from fct_order_item
left join dim_company on fct_order_item.company_id = dim_company.company_id
full join dim_date on fct_order_item.ordered_date = dim_date.actual_date
where dim_company.city = '台北市'

圖表： 202305 - 202405月的台北市月營收 (其中也包含了 McBurger 貢獻的營收）

但在 6 月時，McBurger 搬到台中了，這時如果對資料庫執行 SQL
select * from dim_company where company_name = 'McBurger'
會得到：

如果我們持續用這張 dim_company ，並採用跟剛剛相同的程式碼區追蹤台北市的月營收，會得到下面藍色實線的趨勢 （灰色虛線就是上面那張台北市營收（McBurger 還沒搬家時））的趨勢：

可以發現 202305 - 202405 的營收都減少了，因為隨著 McBurger 搬到台中，原本屬於台北地區的營收也被認列到台中了。

台北市的業績負責人看到這樣的情形，肯定一頭霧水，問道：為什麼我今天一打開儀表板，今年五月以前的營收都減少了呢？

到此為止的 dim_company 都是 SCD Type 1 ，表格中所有欄位都只有該實體的最新資訊，如果資料市集中只有 SCD type 1，顯然無法滿足商業分析的需求，還搞得大家一頭霧水。

SCD type 2

為了解決台北市業績負責人的疑惑，讓資料能夠提供正確的資訊，這時我們需要重新設計 dim_company ，修正成下面的樣子：

同時也把剛剛 追蹤五月營收的 Query 改成：

select
date_trunc(month, dim_date.actual_date) as year_month, 
dim_company.city,
coalesce(sum(fct_order_item.sales_amount), 0) as amount 
from fct_order_item
left join dim_company on fct_order_item.company_id = dim_company.company_id and  fct_order_time.ordered_date between dim_company.valid_from and dim_company.valid_to
full join dim_date on fct_order_item.ordered_date = dim_date.actual_date
where dim_company.city = '台北市'

如此一來，就不會將 McBurger 2024 年 6 月以前的營收誤認到台中市了。

其他的 SCD type

SCD type 其實有不少變化，但在資料市集中最常用的就是 type 1 以及 type 2 。如果對其他類型的 SCD 有興趣，可以參考本屆團友的文章

在資料市集中使用 SCD type 2 的資料模型該如何設計，以及實務中是如何從資料源開始做資料轉換到資料市集，就留待明天詳述。

角色扮演維度表 (Role Playing Dimensions)

另外在實務上也很常遇到一個問題： 同一張維度表對同一張事實表有兩個以上的 join 方式。比方說，訂單可能有下訂日期以及送達日期，他們都可以 join 到 dim_date。此時，dim_date 對於 fct_order_item 來說就扮演著不同的角色：下訂日以及送達日。

SQL 的解決方式也很簡單，只需要換個欄位 join 就好：

-- 台北市每月運送金額：
select
date_trunc(month, dim_date.actual_date) as year_month, 
dim_company.city,
coalesce(sum(fct_order_item.sales_amount), 0) as amount 
from fct_order_item
left join dim_company on fct_order_item.company_id = dim_company.company_id
-- 用 shipped_date 作為 join key
full join dim_date on fct_order_item.shipped_date = dim_date.actual_date
where dim_company.city = '台北市'

但在 PowerBI 中，這是個相當重要的概念，因為他會影響到 Semantic Model 的設計以及定義 Measure (DAX) 的方式，但這就等到 Power BI 時再細說啦。